from datetime import datetime, timedelta
import sqlalchemy
from sqlalchemy import func

from utils.sql.app.user import User
from utils.sql.db import session


# 查询特定时间内每天数据量
# 指定查询时间范围
def get_new_user_increment(day):
    try:
        day = int(day)
        start_date = datetime.now().date() - timedelta(days=day)
        # print(start_date, end_date, timedelta(days=d))
        # 构建查询
        query = (
            session.query(
                func.date(User.create_time).label("date"),
                func.coalesce(func.count(User.user_id), 0).label("count")
            )
            .filter(User.create_time >= start_date)
            .group_by(func.date(User.create_time))
        )

        # 执行查询并获取结果
        # results = query.all()
        date_dict = {date[0]: date[1] for date in query}
        # 返回的日期数组
        rdate = []
        # 返回的数据数组
        rdata = []
        for result in range(day):
            date = start_date + timedelta(days=result+1)
            data = date_dict.get(date, 0)
            rdate.append(date)
            rdata.append(data)
        return {
            "code": 200,
            "data": {
                "date": rdate,
                "data": rdata
            },
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()
